import datetime
import random

holdersh = [
    'E027460051',
    'E000027044',
    'E056644878',
    'E061195753',
    'E054094001',
    'E056539942',
    'E060093306',
    'E054517350',
    'E049326477',
    'E042752855',
    'E044297607',
    'E056706010',
    'E046245567',
    'E048234334',
    'E053850161',
    'E053865263',
    'E062256188',
    'E057403039',
    'E062733465',
    'E051907742',
    'E053929017',
    'E062673932',
    'E049963017',
    'E055617739',
    'E057579597',
    'E057132450',
    'E051191008',
    'E053636347',
    'E053877561',
    'E038459403',
    'E050816574',
    'E053733539',
    'E062853809',
    'E054005078',
    'E050377180',
    'E050244379',
    'E057033363',
    'E050158861',
    'E059932547',
    'E055433729',
    'E059078466',
    'E055255032',
    'E054838421',
    'E055755943',
    'E061117470',
    'E055883584',
    'E061187051',
    'E056552592',
    'E056199784',
    'E054837174',
    'E011216973',
    'E056850576',
    'E053987798',
    'E054324943',
    'E054950524',
    'E054578063',
    'E061190868',
    'E052247713',
    'E061275812',
    'E051753169',
    'E057132442',
    'D890505666',
    'E057736173',
    'E061324776',
    'E054094027',
    'E057988429',
    'E057565310',
    'E061580940',
    'E058495827',
    'E054330936',
    'E059258236',
    'E059284910',
    'E054039394',
    'E062419532',
    'E062389282',
    'E058863787',
    'E061737238',
    'E058877752',
    'E057273531',
    'E059005120',
    'E054584747',
    'E053979193',
    'E066580212',
    'E057504306',
    'E060259601',
    'E060541985',
    'E061986073',
    'E062296015',
    'E060846678',
    'E065125124',
    'E063345697',
    'E063240368',
    'E064226781',
    'E062759267',
    'E063863556',
    'E064256859',
    'E064194536',
    'E064299849',
    'E057770559',
    'E064304953'
]

holdersz = [
    '0602760620',
    '0631392505',
    '0680041455',
    '0680051302',
    '0680034956',
    '0680041178',
    '0680049192',
    '0680036060',
    '0680024797',
    '0680013636',
    '0680015313',
    '0680041629',
    '0680017337',
    '0680020301',
    '0680034254',
    '0680034300',
    '0680055249',
    '0680043237',
    '0680056361',
    '0680030227',
    '0680034479',
    '0680056222',
    '0680026583',
    '0605538059',
    '0680043529',
    '0680042693',
    '0680029408',
    '0680033711',
    '0680034335',
    '0680010035',
    '0680028718',
    '0680033960',
    '0687000012',
    '0680034703',
    '0680027681',
    '0680027349',
    '0680042440',
    '0680027144',
    '0680048758',
    '0680038429',
    '0680046470',
    '0680037990',
    '0680036852',
    '0680039202',
    '0680052058',
    '0680039518',
    '0680052241',
    '0680041211',
    '0605594691',
    '0680036846',
    '0601126199',
    '0680042002',
    '0680034664',
    '0680035589',
    '0605473201',
    '0680036211',
    '0680052250',
    '0680030529',
    '0680052521',
    '0680030060',
    '0680042692',
    '0899035766',
    '0680043767',
    '0680052641',
    '0680034958',
    '0605769182',
    '0680043507',
    '0680053298',
    '0680045228',
    '0680035604',
    '0680046890',
    '0680046975',
    '0680034804',
    '0680055648',
    '0680055594',
    '0680046006',
    '0606133987',
    '0680046032',
    '0680042987',
    '0680046306',
    '0680036225',
    '0680034636',
    '0680066001',
    '0605721782',
    '0605990625',
    '0680050383',
    '0680054523',
    '0680055317',
    '0680051264',
    '0680062138',
    '0680057765',
    '0680057553',
    '0680060017',
    '0680056417',
    '0680059066',
    '0680060091',
    '0680059937',
    '0680060271',
    '0680043832',
    '0680060283'
]

seccodesh = ['600000',
             '600001',
             '600003',
             '600004',
             '600005',
             '600006',
             '600007',
             '600008',
             '600009',
             '600010',
             '600011',
             '600012',
             '600015',
             '600016',
             '600017',
             '600018',
             '600019',
             '600020',
             '600021',
             '600022',
             '600023',
             '600025',
             '600026',
             '600027',
             '600028',
             '600029',
             '600030',
             '600031',
             '600032',
             '600033',
             '600035',
             '600036',
             '600037',
             '600038',
             '600039',
             '600048',
             '600050',
             '600051',
             '600052',
             '600053',
             '600054',
             '600055',
             '600056',
             '600057',
             '600058',
             '600059',
             '600060',
             '600061',
             '600062',
             '600063',
             '600064',
             '600066',
             '600067',
             '600068',
             '600069',
             '600070',
             '600071',
             '600072',
             '600073',
             '600074',
             '600075',
             '600076',
             '600077',
             '600078',
             '600079',
             '600080',
             '600081',
             '600082',
             '600083',
             '600084',
             '600085',
             '600086',
             '600087',
             '600088',
             '600089',
             '600090',
             '600091',
             '600093',
             '600094',
             '600095',
             '600096',
             '600097',
             '600098',
             '600099',
             '600100',
             '600101',
             '600102',
             '600103',
             '600104',
             '600105',
             '600106',
             '600107',
             '600108',
             '600109',
             '600110',
             '600111',
             '600112',
             '600113',
             '600114',
             '600115']
seccodesz = ['000001',
             '000002',
             '000004',
             '000005',
             '000006',
             '000007',
             '000008',
             '000009',
             '000010',
             '000011',
             '000012',
             '000014',
             '000016',
             '000017',
             '000018',
             '000019',
             '000020',
             '000021',
             '000022',
             '000023',
             '000024',
             '000025',
             '000026',
             '000027',
             '000028',
             '000029',
             '000030',
             '000031',
             '000032',
             '000033',
             '000034',
             '000035',
             '000036',
             '000037',
             '000038',
             '000039',
             '000040',
             '000042',
             '000043',
             '000045',
             '000046',
             '000048',
             '000049',
             '000050',
             '000055',
             '000056',
             '000058',
             '000059',
             '000060',
             '000061',
             '000062',
             '000063',
             '000065',
             '000066',
             '000068',
             '000069',
             '000070',
             '000078',
             '000088',
             '000089',
             '000090',
             '000096',
             '000099',
             '000100',
             '000150',
             '000151',
             '000153',
             '000155',
             '000156',
             '000157',
             '000158',
             '000159',
             '000166',
             '000301',
             '000333',
             '000338',
             '000400',
             '000401',
             '000402',
             '000403',
             '000404',
             '000407',
             '000408',
             '000409',
             '000410',
             '000411',
             '000413',
             '000415',
             '000416',
             '000417',
             '000418',
             '000419',
             '000420',
             '000421',
             '000422',
             '000423',
             '000425',
             '000426',
             '000428',
             '000429']
rdate = datetime.datetime.today().date().strftime("%Y%m%d")
# holderaccsh = 'E000211279'
# holderaccsz = '0600021034'
DTD_SERIAL_NO = 1
_sqlcount = 0
_count = 0
f = open('file.sql', mode='a', encoding='utf8')
for i in range(1, 500):
    _count = random.randint(1, 100)-1
    sqlsh = f"INSERT INTO KS.DIVIDENDS_TAX_IMPORT (RECORD_NO, CREATE_DATE, MARKET_CODE, DIVIDENDS_TAX_DATE, DTD_SERIAL_NO, IMPORT_FILE, CP_CODE, SEAT_NO, BUSINESS_TYPE, CURRENCY_TYPE, HOLDER_ACC_NO, SEC_CODE, SEC_TYPE, INITIAL_LOAD_DATE, UNLOAD_DATE, DIVIDENDS_TAX, TAX_PRICE, TAX_RATE, UNLOAD_VOL, ERR_CODE, ERR_MSG) \
    VALUES ({DTD_SERIAL_NO}, {rdate}, '1', {rdate}, '{DTD_SERIAL_NO}', 'ABCSJ', 'JSX25', '22808', 'HLS', '01', '{holdersh[_count]}', '{seccodesh[_count]}', '66', 20200506, {rdate}, 100, 3.5, 0.1, 11000, '', '');\n"
    DTD_SERIAL_NO = DTD_SERIAL_NO + 1
    print(sqlsh)
    _sqlcount += 1
    f.writelines(sqlsh)
for i in range(1, 5000):
    _count = random.randint(1, 100) - 1
    sqlsh = f"INSERT INTO KS.DIVIDENDS_TAX_IMPORT (RECORD_NO, CREATE_DATE, MARKET_CODE, DIVIDENDS_TAX_DATE, DTD_SERIAL_NO, IMPORT_FILE, CP_CODE, SEAT_NO, BUSINESS_TYPE, CURRENCY_TYPE, HOLDER_ACC_NO, SEC_CODE, SEC_TYPE, INITIAL_LOAD_DATE, UNLOAD_DATE, DIVIDENDS_TAX, TAX_PRICE, TAX_RATE, UNLOAD_VOL, ERR_CODE, ERR_MSG) \
    VALUES ({DTD_SERIAL_NO}, {rdate}, '2', {rdate}, '{DTD_SERIAL_NO}', 'ZSMX', '650103', '394595', 'ZS', '01', '{holdersz[_count]}', '{seccodesz[_count]}', '66', 20200506, {rdate}, 100, 3.5, 0.1, 11000, '', '');\n"
    DTD_SERIAL_NO = DTD_SERIAL_NO + 1
    print(sqlsh)
    _sqlcount += 1
    f.writelines(sqlsh)
f.close()
print(f"总共生成记录数{_sqlcount}")
